package com.stack.knowyoudao.dao;

import com.stack.knowyoubean.bean.Answer;
import org.apache.ibatis.annotations.*;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * 答案表持久层
 *
 * @author stack
 */
@Component
@Scope("singleton")
// 开启 mybatis 二级缓存
@CacheNamespace
public interface AnswerDao {

    /**
     * 统计作业完成情况
     *
     * @param sid  课程号
     * @param hkid 作业分类
     * @return 完成情况数据
     */
    @Select("select hname as xData,count(answer.content) as yData from homework,answer where homework.hid=answer.hid and sid=#{sid} and hkid=#{hkid} group by homework.hid")
    List<Map<String, Object>> findEndAnswerCount(@Param("sid") Integer sid, @Param("hkid") Integer hkid);

    /**
     * 查询学生综合成绩排名
     *
     * @param sid 学号
     * @return 排名集合
     */
    @Select("select username as xData,sum(score) as yData from answer,user,homework where answer.stuid=user.userid and answer.hid=homework.hid and sid=#{sid} group by answer.stuid order by yData desc")
    List<Map<String, Object>> findScoreRate(@Param("sid") Integer sid);

    /**
     * 查询得分比值（分组，教师，学生,连接时注意左连接还是右连接）
     *
     * @param userid 学号（可有可无动态sql）
     * @param sid    课程号
     * @return
     */
    @Select("<script>" +
            "select hkname as xData,sum(score) as yData from homework,homeworkkind,answer " +
            "where homework.hkid=homeworkkind.hkid and homework.hid=answer.hid " +
            "<if test=\" stuid!=null and stuid != '' \"> and answer.stuid=#{stuid} </if> " +
            "and homeworkkind.hkid !=1  and sid=#{sid} group by homeworkkind.hkid" +
            "</script>"
    )
    List<Map<String, Object>> findHomeWorkRadio(@Param("stuid") Integer userid, @Param("sid") Integer sid);

    /**
     * 查询课后或实验作业得分情况（首页数据分析）
     *
     * @param hkid   作业类型
     * @param sid    课程
     * @param userid 学号
     * @return 成绩集合
     */
    @Select("select hname as xData,score as yData from homework,answer where homework.hid=answer.hid and hkid=#{hkid} and stuid=#{stuid} and sid=#{sid}")
    List<Map<String, Object>> findHomeWorkScore(@Param("hkid") Integer hkid, @Param("sid") Integer sid, @Param("stuid") Integer userid);


    /**
     * 查询考试成绩集合(考试成绩分析)
     *
     * @param hid 作业号
     * @return 成绩集合
     */
    @Select("select score as name,count(*) as value from answer where hid=#{hid} group by score")
    List<Map<String, Object>> findAnswerScore(@Param("hid") Integer hid);

    /**
     * 预习作答集合（数据分析）
     *
     * @param hid 作业号
     * @return 预习内容集合
     */
    @Select("select content from answer where hid=#{hid}")
    List<String> findPreAnswer(@Param("hid") Integer hid);

    /**
     * 查询学生作答答案
     *
     * @param hid   作业号
     * @param stuid 学号
     * @return 答案信息
     */
    @Select("select * from answer where hid=#{hid} and stuid=#{stuid}")
    Answer findAnswerByHid(@Param("hid") Integer hid, @Param("stuid") Integer stuid);

    /**
     * 添加一条学生作答记录
     *
     * @param answer 答案
     */
    @Insert("insert into answer(hid,stuid) values(#{hid},#{stuid})")
    void insertAnswer(Answer answer);

    /**
     * 更新作业表中内容
     *
     * @param answer 作业表实体
     * @return 更新成功标记
     */
    @Update("update answer set content=#{content},score=#{score},comment=#{comment} where hid=#{hid} and stuid=#{stuid}")
    int updateAnswer(Answer answer);


    /**
     * 学生作答列表
     *
     * @param hid     作业号
     * @param stuid   学号
     * @param score   成绩
     * @param stuname 用户名
     * @return 总数
     */
    @Select("<script>" +
            "select count(*) from answer,user where answer.stuid = user.userid " +
            "and hid=#{hid}" +
            "<if test=\" stuid!=null and stuid != '' \"> and user.userid=#{stuid} </if> " +
            "<if test=\" score!=null and score != '' \"> and answer.score=#{score} </if>" +
            "<if test=\" stuname!=null and stuname != '' \"> and user.username like  #{stuname} </if>" +
            "</script>")
    Integer findAnswerCount(@Param("hid") Integer hid, @Param("stuid") Integer stuid, @Param("score") Integer score, @Param("stuname") String stuname);

    /**
     * 学生作答列表(排序)
     *
     * @param hid     作业号
     * @param stuid   学号
     * @param score   成绩
     * @param stuname 用户名
     * @param page    页码
     * @param limit   数量
     * @return 学生作答答案记录
     */
    @Select("<script>" +
            "select answer.hid,answer.stuid,answer.score,user.username as stuname from answer,user where answer.stuid = user.userid " +
            "and hid=#{hid}" +
            "<if test=\" stuid!=null and stuid != '' \"> and user.userid=#{stuid} </if> " +
            "<if test=\" score!=null and score != '' \"> and answer.score=#{score} </if>" +
            "<if test=\" stuname!=null and stuname != '' \"> and user.username like  #{stuname} </if>" +
            "order by score desc " +
            "limit #{page},#{limit}" +
            "</script>")
    List<Answer> findAnswer(@Param("hid") Integer hid, @Param("stuid") Integer stuid, @Param("score") Integer score, @Param("stuname") String stuname, @Param("page") Integer page, @Param("limit") Integer limit);
}
